/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package webbank;

import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

/**
 *
 * @author Johan
 */
public class DatabaseHandler {

    private String dbClass;
    private Statement statement;
    private Connection con;
    private String dbUrl = "jdbc:mysql://localhost/webbank";
    private String dbUsername = "root";
    private String dbPassword = "asioeigh31";

    public DatabaseHandler() {

        this.dbClass = "com.mysql.jdbc.Driver";
        startConnection();
    }
    //Used to open a connection to the database and creating a statement for query execution

    private void startConnection() {
        try {
            System.out.println(dbUrl);
            con = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
            statement = con.createStatement();
            JOptionPane.showMessageDialog(null, "connection started", "NOT ERROR", JOptionPane.PLAIN_MESSAGE);
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error in starting connection, try again or check your database settings", "ERROR", JOptionPane.ERROR_MESSAGE);
        }
    }
    //Returns a ResultSet of the query parameter

    public ResultSet executeSelect(String query) throws SQLException {
        ResultSet rs = null;

        try {
            Class.forName(dbClass);
            rs = statement.executeQuery(query);


        } catch (ClassNotFoundException | SQLException e) {
            JOptionPane.showMessageDialog(null, "Error in executing update-query, try again or check your database settings", "ERROR", JOptionPane.ERROR_MESSAGE);
            Logger.getLogger(this.getClass().getName(), e.getMessage());
        }

        return rs;
    }
    //Executes a update depending on the query parameter, automatically opens and closes the database connection

    public void executeUpdate(String query) {

        try {
            Class.forName(dbClass);
            int r = statement.executeUpdate(query);

        } catch (ClassNotFoundException | SQLException e) {
            JOptionPane.showMessageDialog(null, "Error in executing update-query, try again or check your database settings", "ERROR", JOptionPane.ERROR_MESSAGE);
            Logger.getLogger(this.getClass().getName(), e.getMessage());
        }
    }

    public String getPassword(String civicReg) throws SQLException {
        String query = "SELECT Password FROM accountholder WHERE CivicReg = " + civicReg;
        ResultSet rs = executeSelect(query);
        while (rs.next()) {
            return rs.getString(1);
        }
        return "";
    }

    public boolean setBalance(String accountNumber,int amount) {
        String query = "SELECT * FROM account WHERE idAccount = "+accountNumber;
        try {
            ResultSet rs = executeSelect(query);
            if(rs == null){
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        query = "UPDATE account SET Resources = Resources +"+amount+" WHERE idAccount = "+accountNumber;
        executeUpdate(query);
        return true;
    }
    
    public int getBalance(String accountNumber) {
        String query = "SELECT Resources FROM account WHERE idAccount = "+accountNumber;
        try {
            ResultSet rs = executeSelect(query);
            if(rs == null){
                return -1; 
            }
            while(rs.next()){
                return rs.getInt(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }

    public String[] getAccounts(String civicReg) {
        String query = "SELECT idAccount FROM account WHERE AccountHolder_CivicReg = " + civicReg;
        ResultSet rs = null;
        //  ArrayList accounts = new ArrayList();
        String[] accounts = null;
        int counter = 0;
        int rowCount;
        try {
            rs = executeSelect(query);
            if (rs.last()) {
                rowCount = rs.getRow();
                rs.beforeFirst(); 
                accounts = new String[rowCount];
            }    
            while (rs.next()) {
                accounts[counter] = rs.getString(1);
                counter++;
                //System.out.println(accounts[counter]);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseHandler.class.getName()).log(Level.SEVERE, null, ex);
        }


        return accounts;
    }
}
